IF OBJECT_ID('tempdb..#tmpAgentLeadCalender') IS NOT NULL
BEGIN
    DROP TABLE #tmpAgentLeadCalender;
END

CREATE TABLE #tmpAgentLeadCalender(
	ID int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	fDate date,
	fTime time(0),
	cbCount int,
	fCount int
);
GO
Declare @StartDate date
Declare @EndDate date
Declare @AgentID bigint

SET @StartDate = GetDate() - 2--Convert(varchar(8), GetDate() - 3, 112)
SET @EndDate = GetDate() + 4 --Convert(varchar(8), GetDate() + 3, 112)
SET @AgentID = 18808
--Select @StartDate, @EndDate



--DECLARE @@tblAgentPendingLead TABLE 
--(
--	ID int NOT NULL IDENTITY (1, 1),
--	fDate date,
--	fTime time,
--	cbCount int,
--	fCount int
--)

Declare @tmpHr smallint
Declare @tmpCBCount smallint
Declare @tmpFCount smallint
Declare @tmpColumn varchar(MAX)
SET @tmpColumn = ''
SET @tmpHr = 8
SET @tmpCBCount = 0
SET @tmpFCount = 0
--Select Cast(CAST(@tmpHr as varchar) + ':00:00' as time)
SET @tmpColumn = '[' + Convert(varchar(20), @StartDate, 113) + ']'
WHILE @StartDate <= @EndDate
BEGIN
	SET @tmpHr = 9
	
	WHILE @tmpHr < 19
	BEGIN
		Select @tmpCBCount = Count(CXCRMID) from tblCXCRMCallStatus
		Where CallFinishType = 2 
		And AgentID = @AgentID
		And CallBackDateTime Between (Convert(varchar(20), @StartDate, 111) + ' ' + CAST(@tmpHr as varchar) + ':00:00') AND (Convert(varchar(20), @StartDate, 111) + ' ' + CAST(@tmpHr + 1 as varchar) + ':00:00')
		
		IF @tmpCBCount IS NULL SET @tmpCBCount = 0
		
		Select @tmpCBCount = @tmpCBCount + Count(CXCRMID) from tblCXCRMCallStatus
		Where CallFinishType = 3 
		And AgentID = @AgentID
		And (ModifiedDate + 1) Between (Convert(varchar(20), @StartDate, 111) + ' ' + CAST(@tmpHr as varchar) + ':00:00') AND (Convert(varchar(20), @StartDate, 111) + ' ' + CAST(@tmpHr + 1 as varchar) + ':00:00')
		
		IF @tmpCBCount IS NULL SET @tmpCBCount = 0
		
		INSERT INTO #tmpAgentLeadCalender
		Values(@StartDate, Cast(CAST(@tmpHr as varchar) + ':00:00' as time), @tmpCBCount, @tmpFCount)
		SET @tmpHr = @tmpHr + 1
	END
	SET @StartDate = DateAdd(day, 1, @StartDate)
	IF(@StartDate <= @EndDate)
	BEGIN
		SET @tmpColumn = @tmpColumn + ', [' + Convert(varchar(20), @StartDate, 113) + ']'
	END
END

--Select Convert(varchar(20), fDate, 113), * from #tmpAgentLeadCalender
--Select @tmpColumn

Declare @Query varchar(Max)

SET @Query = N'SELECT CONVERT(varchar(10), fTime, 100) + '' to '' + CONVERT(varchar(10), DateAdd(hh, 1, fTime), 100) AppTime, ' + @tmpColumn + '
FROM (
SELECT Convert(varchar(20), fDate, 113) pvDate, fTime, cbCount, fCount
FROM #tmpAgentLeadCalender) t
PIVOT (SUM(cbCount) FOR pvDate IN ('+@tmpColumn+')) AS pvt
ORDER BY fTime
'
PRINT @Query
EXECUTE(@Query)

--Declare @StartDate datetime
--Declare @EndDate datetime

--SET @StartDate = '24/Apr/2013' + ' 9:00:00'
--SET @EndDate = '24/Apr/2013' + ' 10:00:00'

--select top 10 * from tblCXCRMCallStatus 
--Where AgentID = 18808 And CallFinishType = 2 
--And CallBackDateTime Between @StartDate And @EndDate

--select top 10 * from tblCXCRMCallStatus 
--Where AgentID = 18808 And CallFinishType = 2 
--Order By CallBackDateTime ASC

--Declare @testDateTime datetime
--Select Convert(varchar(20), GetDate(), 111) + ' 23:00:00'
--SET @testDateTime = Convert(varchar(20), GetDate(), 111) + ' 23:00:00'
--Select @testDateTime

--select CONVERT(varchar(15),CAST('17:30:00.0000000' AS TIME),100)

--select * from tblCXCRMCallStatus 
--Where AgentID = 18808 And CallFinishType = 2 
--And CallBackDateTime Between '25-Apr-13 10:00AM' AND '25-Apr-13 11:00AM'
--Order By CallBackDateTime ASC

--Declare @tTime as time(0)
--SET @tTime = '10:00:00'

--Select DateAdd(hh, 12, @tTime)